
from SKO.AbstractDPJob import AbstractDPJob
import sys, datetime, json
import pandas as pd
import numpy as np
from numpy import array
import cvxpy as cp
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
import os
from SKO.PSO import PSO
class MoxingjiaoJob(AbstractDPJob):


    def __init__(self,
                 p_tmpl_no=None):

        super(MoxingjiaoJob, self).__init__()
        self.tmpl_no = p_tmpl_no
        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):

        super(MoxingjiaoJob, self).do_execute()

        tmpl_no = self.tmpl_no
        # 数据库配置写死
        # DB_HOST_MPP_DB2 = '10.70.48.41'
        # DB_PORT_MPP_DB2 = 50021
        # DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        # DB_USER_MPP_DB2 = 'g0mazzai'
        # DB_PASSWORD_MPP_DB2 = 'g0mazzaibg00'
        #
        # # 数据库连接函数写死
        # def getConnectionDb2(host, port, dbname, user, password):
        #     # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
        #     engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
        #                            encoding="utf-8", poolclass=NullPool)
        #     return engine.connect()
        #
        # db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
        #                                DB_PORT_MPP_DB2,
        #                                DB_DBNAME_MPP_DB2,
        #                                DB_USER_MPP_DB2,
        #                                DB_PASSWORD_MPP_DB2)
        # PSO的参数
        w = 0.8  # 惯性因子，一般取1
        c1 = 2  # 学习因子，一般取2
        c2 = 2  #
        # dim = 20  # 维度的维度
        size = 100  # 种群大小，即种群中小鸟的个数
        iter_num = 100  # 算法最大迭代次数
        # max_vel = 0.5  # 限制粒子的最大速度
        max_vel = 1

        max_constant = 1000000
        # 读数据
        # sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,UNIT_PRICE,INIT_INV,INV_WT,MERGE_FLAG as OLD_FLAG " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE2 " \
        #       " where TMPL_NO ='%s' and VAR !='其他参数' order by REC_ID " % (tmpl_no)
        # data_meizhong = pd.read_sql_query(sql, con=db_conn_mpp)
        data_meizhong = pd.read_excel('COKE2价格.xlsx')
        data_meizhong.columns = data_meizhong.columns.str.upper()
        # sql = " select VAR,PROD_DSCR,UNIT_PRICE " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE2 " \
        #       " where TMPL_NO ='%s' and VAR='其他参数' " % (tmpl_no)
        # data_otherprice = pd.read_sql_query(sql, con=db_conn_mpp)
        data_otherprice = pd.read_excel('COKE2其他参数价格.xlsx')
        data_otherprice.columns = data_otherprice.columns.str.upper()
        data_other1 = data_otherprice[data_otherprice['PROD_DSCR'] == '外购焦炭']
        data_other1 = data_other1.reset_index(drop=True)
        price_waigoujiaotan = data_other1.loc[0]['UNIT_PRICE']
        # sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,MIN_VALUE,MAX_VALUE,FLAG " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO2 " \
        #       " where TMPL_NO ='%s'  " % (tmpl_no)
        # data_bili = pd.read_sql_query(sql, con=db_conn_mpp)
        data_bili = pd.read_excel('COKE2比例上下限.xlsx')
        data_bili.columns = data_bili.columns.str.upper()
        # sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,H2O,ASH,COKE_VM,S, " \
        #       " COKING_COALBLD_BOND_IND,M_L3_JZCHD_Y,COKING_COALBLD_GIESFLU,L_M_AB,C,COKE_HOTVALUE " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_IND_INFO2 " \
        #       " where TMPL_NO ='%s' " % (tmpl_no)
        # data_meizhi = pd.read_sql_query(sql, con=db_conn_mpp)
        data_meizhi = pd.read_excel('COKE2煤质.xlsx')
        data_meizhi.columns = data_meizhi.columns.str.upper()
        # sql = " select BIG_VAR,FLAG,H2O,ASH,COKE_VM,S, " \
        #       " COKING_COALBLD_BOND_IND,M_L3_JZCHD_Y,COKING_COALBLD_GIESFLU,L_M_AB,C,COKE_HOTVALUE " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_INDLIMIT2 " \
        #       " where TMPL_NO ='%s' " % (tmpl_no)
        # data_shangxiaxian = pd.read_sql_query(sql, con=db_conn_mpp)
        data_shangxiaxian = pd.read_excel('COKE2煤质上下限.xlsx')
        data_shangxiaxian.columns = data_shangxiaxian.columns.str.upper()
        # sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,FE2O3,SIO2,CAO,AL2O3,MGO,FOP_TIO2, " \
        #       " MNO,P2O5,BF_SINTER_QLTY_S,ZN,PB,FOP_K2O,FOP_NA2O,VITRI_REFL,VITRI_COAL_OI " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_ASH_CPETRO " \
        #       " where TMPL_NO ='%s' " % (tmpl_no)
        # data_huizhongchengfen = pd.read_sql_query(sql, con=db_conn_mpp)
        data_huizhongchengfen = pd.read_excel('COKE2灰中成分煤岩分析.xlsx')
        data_huizhongchengfen.columns = data_huizhongchengfen.columns.str.upper()
        # sql = " select PARM_CHN,PARM_CALC " \
        #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_COEF2 " \
        #       " where TMPL_NO ='original' "
        # data_jiaotancanshu = pd.read_sql_query(sql, con=db_conn_mpp)
        data_jiaotancanshu = pd.read_excel('COKE2参数.xlsx')
        data_jiaotancanshu.columns = data_jiaotancanshu.columns.str.upper()
        parm_1 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_1')]
        parm_1 = parm_1['PARM_CALC'].values[0]
        parm_2 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_2')]
        parm_2 = parm_2['PARM_CALC'].values[0]
        parm_3 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_3')]
        parm_3 = parm_3['PARM_CALC'].values[0]
        parm_4 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_4')]
        parm_4 = parm_4['PARM_CALC'].values[0]
        parm_5 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_5')]
        parm_5 = parm_5['PARM_CALC'].values[0]
        parm_6 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_6')]
        parm_6 = parm_6['PARM_CALC'].values[0]
        parm_7 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_7')]
        parm_7 = parm_7['PARM_CALC'].values[0]
        parm_8 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_8')]
        parm_8 = parm_8['PARM_CALC'].values[0]
        parm_10 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_10')]
        parm_10 = parm_10['PARM_CALC'].values[0]
        parm_11 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_11')]
        parm_11 = parm_11['PARM_CALC'].values[0]
        parm_12 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_12')]
        parm_12 = parm_12['PARM_CALC'].values[0]
        parm_13 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_13')]
        parm_13 = parm_13['PARM_CALC'].values[0]
        parm_14 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_14')]
        parm_14 = parm_14['PARM_CALC'].values[0]
        parm_15 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_15')]
        parm_15 = parm_15['PARM_CALC'].values[0]
        parm_16 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_16')]
        parm_16 = parm_16['PARM_CALC'].values[0]
        parm_17 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_17')]
        parm_17 = parm_17['PARM_CALC'].values[0]
        parm_19 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_19')]
        parm_19 = parm_19['PARM_CALC'].values[0]
        parm_20 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_20')]
        parm_20 = parm_20['PARM_CALC'].values[0]
        parm_21 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_21')]
        parm_21 = parm_21['PARM_CALC'].values[0]
        parm_22 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_22')]
        parm_22 = parm_22['PARM_CALC'].values[0]
        parm_23 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_23')]
        parm_23 = parm_23['PARM_CALC'].values[0]
        parm_24 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_24')]
        parm_24 = parm_24['PARM_CALC'].values[0]
        parm_25 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_25')]
        parm_25 = parm_25['PARM_CALC'].values[0]
        parm_26 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_26')]
        parm_26 = parm_26['PARM_CALC'].values[0]
        parm_27 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_27')]
        parm_27 = parm_27['PARM_CALC'].values[0]
        parm_28 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_28')]
        parm_28 = parm_28['PARM_CALC'].values[0]
        parm_29 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_29')]
        parm_29 = parm_29['PARM_CALC'].values[0]

        parm_31 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_31')]
        parm_31 = parm_31['PARM_CALC'].values[0]
        parm_32 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_32')]
        parm_32 = parm_32['PARM_CALC'].values[0]
        parm_33 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_33')]
        parm_33 = parm_33['PARM_CALC'].values[0]
        parm_34 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_34')]
        parm_34 = parm_34['PARM_CALC'].values[0]
        parm_35 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_35')]
        parm_35 = parm_35['PARM_CALC'].values[0]
        parm_36 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_36')]
        parm_36 = parm_36['PARM_CALC'].values[0]
        parm_37 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_37')]
        parm_37 = parm_37['PARM_CALC'].values[0]
        parm_38 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_38')]
        parm_38 = parm_38['PARM_CALC'].values[0]
        parm_39 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_39')]
        parm_39 = parm_39['PARM_CALC'].values[0]
        parm_40 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_40')]
        parm_40 = parm_40['PARM_CALC'].values[0]
        parm_41 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_41')]
        parm_41 = parm_41['PARM_CALC'].values[0]
        parm_42 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_42')]
        parm_42 = parm_42['PARM_CALC'].values[0]
        parm_43 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_43')]
        parm_43 = parm_43['PARM_CALC'].values[0]
        parm_44 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_44')]
        parm_44 = parm_44['PARM_CALC'].values[0]
        parm_45 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_45')]
        parm_45 = parm_45['PARM_CALC'].values[0]
        parm_46 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_46')]
        parm_46 = parm_46['PARM_CALC'].values[0]
        parm_47 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_47')]
        parm_47 = parm_47['PARM_CALC'].values[0]
        parm_48 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_48')]
        parm_48 = parm_48['PARM_CALC'].values[0]
        QRT1 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'QRT1')]
        QRT1 = QRT1['PARM_CALC'].values[0]
        QRT2 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'QRT2')]
        QRT2 = QRT2['PARM_CALC'].values[0]
        QRT3 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'QRT3')]
        QRT3 = QRT3['PARM_CALC'].values[0]
        QRT4 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'QRT4')]
        QRT4 = QRT4['PARM_CALC'].values[0]
        # 去除资源量为0的品种，减少变量
        # 月初库存
        data_meizhong.INIT_INV.fillna(0, inplace=True)
        # 可供资源
        data_meizhong.INV_WT.fillna(0, inplace=True)

        data_qianti = pd.read_excel('COKE2前提条件.xlsx')
        data_qianti.columns = data_qianti.columns.str.upper()

        data_zhuyao = pd.read_excel('COKE2主要参数.xlsx')
        data_zhuyao.columns = data_zhuyao.columns.str.upper()

        # 年度前提条件表
        kongmeizhibiao = data_qianti.loc[0]['TOTAL_COAL_AMT']
        tieshuichanliang = data_qianti.loc[0]['MOLTIRON_WT']
        jiaotanchanliang = data_qianti.loc[0]['COKE_OUTPUT']
        fadianliang = data_qianti.loc[0]['ELECOUT']
        shaojiechanliang = data_qianti.loc[0]['SINTER_OUTPUT']
        zongmeibi = data_qianti.loc[0]['BF_PCR']
        CDQbi = data_qianti.loc[0]['CDQ_RATE']

        # 纯煤比 = 总煤比 - CDQ比
        chunmeibi = data_qianti.loc[0]['BF_PCR1']
        zongjiaobi = data_qianti.loc[0]['BF_CR']
        shaojieranliaobi = data_qianti.loc[0]['SINTER_FUEL_RATIO']
        # 主要参数表
        chengjiaolv = data_zhuyao.loc[0]['COKING_COKEYR']
        yejinjiaolv = data_zhuyao.loc[0]['COKING_METCOKRAT']
        cujiaolv = data_zhuyao.loc[0]['SINTER_CRSCOKE_RATIO']
        meidianbi = data_zhuyao.loc[0]['COAL_ELEC_RATIO']
        fadianmeihao = data_zhuyao.loc[0]['COAL_USE']
        lianjiaomeishuifen = data_zhuyao.loc[0]['COKING_COALBLD_MOIST']
        penchuimeishuifen = data_zhuyao.loc[0]['BF_COALINJECT_MOIST']
        shaojiemeishuifen = data_zhuyao.loc[0]['SINTER_COAL_MOIST']
        penchui_meitanwushunlv = data_zhuyao.loc[0]['COAL_LOSS_RATE1']
        fadian_meitanwushunlv = data_zhuyao.loc[0]['COAL_LOSS_RATE2']
        shaojie_meitanwushunlv = data_zhuyao.loc[0]['COAL_LOSS_RATE3']
        lianjiao_meitanwushunlv = data_zhuyao.loc[0]['COAL_LOSS_RATE4']
        waigoujiaoshuifen = data_zhuyao.loc[0]['PCOKE_MOIST']
        waigoujiaofenlv = data_zhuyao.loc[0]['PCOKE_COKEPOWD_RATE']
        shaojiejiaofenshuifen = data_zhuyao.loc[0]['SINTER_COKE_MOIST']
        lantanshuifen = data_zhuyao.loc[0]['SEMICOKE_MOIST']
        jiaotanchaochanlv = data_zhuyao.loc[0]['COKING_OVERPROD_RATE']

        CAL_WT_lianjiaomei = jiaotanchanliang / chengjiaolv * 100 / (100 - lianjiaomeishuifen) * 100 * (
                100 + lianjiao_meitanwushunlv) / 100
        WT_lianjiaomei = CAL_WT_lianjiaomei * (100 + jiaotanchaochanlv) / 100
        # 外购焦炭    （补炼焦煤）
        WT_waigoujiaotan = (tieshuichanliang * zongjiaobi / 1000 - jiaotanchanliang * yejinjiaolv / 100) / (
                100 - waigoujiaofenlv) * 100 / (100 - waigoujiaoshuifen) * 100

        def __cal_resource_wt(x):
            rst = x.INIT_INV + x.INV_WT
            return rst

        data_meizhong['MAX_WT'] = data_meizhong.apply(lambda x: __cal_resource_wt(x), axis=1)

        data_meizhong = data_meizhong[(data_meizhong['MAX_WT'] > 0)]
        data_meizhong = data_meizhong.reset_index(drop=True)
        # ganmeiliang = data_meizhong['PLAN_WT'].sum()
        # peihemei_wt = data_meizhong['PLAN_WT'].sum()
        peihemei_wt = WT_lianjiaomei
        my_list = data_meizhong['OLD_FLAG'].values.tolist()
        print(my_list)
        import numpy as np
        new_list = [x for x in my_list if not np.isnan(x)]
        print(new_list)
        sorted_unique_list = sorted(set(new_list))
        print(sorted_unique_list)

        def __cal_new_merge_flag(x):
            rst = 0
            if x.OLD_FLAG in sorted_unique_list:
                rst = sorted_unique_list.index(x.OLD_FLAG) + 1
            return rst

        data_meizhong['MERGE_FLAG'] = data_meizhong.apply(lambda x: __cal_new_merge_flag(x), axis=1)
        data_meizhong.drop(['OLD_FLAG'], axis=1, inplace=True)

        # 对煤种生成唯一编码mark
        # 维度上只考虑VAR性状，SOURCE来源
        # 写死维度种类对应关系？
        data_mark = data_meizhong.copy()

        def __cal_rank_xingzhuang(x):
            if x.VAR == '主焦':
                rst = 1
            elif x.VAR == '肥煤':
                rst = 2
            elif x.VAR == '1/3焦':
                rst = 3
            elif x.VAR == '气煤':
                rst = 4
            elif x.VAR == '瘦煤':
                rst = 5
            else:
                rst = 6
            return rst

        data_mark['xingzhuang_rank'] = data_mark.apply(lambda x: __cal_rank_xingzhuang(x), axis=1)

        def __cal_rank_laiyuan(x):
            if x.SOURCE == '进口':
                rst = 1
            elif x.SOURCE == '大矿长协':
                rst = 2
            elif x.SOURCE == '大矿市场':
                rst = 3
            elif x.SOURCE == '地方矿':
                rst = 4
            else:
                rst = 5
            return rst

        data_mark['laiyuan_rank'] = data_mark.apply(lambda x: __cal_rank_laiyuan(x), axis=1)

        def __cal_rank_group(x):
            rst = str(x.xingzhuang_rank) + '_' + str(x.laiyuan_rank)
            return rst

        data_mark['group'] = data_mark.apply(lambda x: __cal_rank_group(x), axis=1)
        data_mark = data_mark.reset_index(drop=False)
        data_mark.rename(columns={'index': 'index_old'}, inplace=True)

        data_mark['rank0'] = data_mark['index_old'].groupby(data_mark['group']).rank()
        data_mark['pinming_rank'] = data_mark['rank0'].astype(int)

        def __cal_rank_mark(x):
            rst = str(x.group) + '_' + str(x.pinming_rank)
            return rst

        data_mark['mark'] = data_mark.apply(lambda x: __cal_rank_mark(x), axis=1)
        data_mark.drop(['rank0'], axis=1, inplace=True)
        # 统计个数，做个var_df以便后续对应
        var_list = ['主焦', '肥煤', '1/3焦', '气煤', '瘦煤']
        source_list = ['进口', '大矿长协', '大矿市场', '地方矿']
        var_df = pd.DataFrame(columns=['VAR', 'SOURCE', 'group', 'count'])
        dict = {}
        for i in range(1, len(var_list) + 1):
            for j in range(1, len(source_list) + 1):
                df = data_mark[
                    (data_mark['VAR'] == var_list[i - 1]) & (data_mark['SOURCE'] == source_list[j - 1])]
                row_count = df.shape[0]
                # print("行数：", row_count)
                dict['VAR'] = var_list[i - 1]
                dict['SOURCE'] = source_list[j - 1]
                dict['group'] = str(i) + '_' + str(j)
                dict['count'] = row_count
                new_row = pd.Series(dict)
                var_df = var_df.append(new_row, ignore_index=True)
        # 生成动态参数
        # 资源量，分配比例上下限
        # 资源量，按照品名的
        data_ziyuanliang_1 = data_mark[
            ['PROD_CODE', 'INIT_INV', 'INV_WT', 'MERGE_FLAG', 'MAX_WT', 'mark']]
        data_ziyuanliang_1.MAX_WT.fillna(0, inplace=True)
        for index, row in data_ziyuanliang_1.iterrows():
            exec('ziyuanliang_{} ={}'.format(row['mark'], row['MAX_WT']))
        # 资源量，合并单元格的
        merge_num = data_mark["MERGE_FLAG"].max()
        print(merge_num)
        for i in range(1, int(merge_num) + 1):
            print(i)
            data_ziyuanliang_2 = data_ziyuanliang_1[(data_ziyuanliang_1['MERGE_FLAG'] == i)]
            data_ziyuanliang_2 = data_ziyuanliang_2.reset_index(drop=True)
            tmp_merge_ziyuanliang = data_ziyuanliang_2.loc[0]['INV_WT']
            exec('merge_ziyuanliang_{} ={}'.format(i, tmp_merge_ziyuanliang))
        # 分配比例上下限，按照品名的
        data_bili.rename(columns={'MAX_VALUE': 'UL'}, inplace=True)
        data_bili.rename(columns={'MIN_VALUE': 'LL'}, inplace=True)
        data_bili.UL.fillna(100, inplace=True)
        data_bili.LL.fillna(0, inplace=True)
        data_bili_1 = data_bili[(data_bili['FLAG'] == '品名')]
        data_bili_1 = data_bili_1.reset_index(drop=True)
        data_bili_1 = data_bili_1[['PROD_CODE', 'UL', 'LL']]
        v = ['PROD_CODE']
        df3 = pd.merge(data_mark, data_bili_1, on=v, how='left')
        for index, row in df3.iterrows():
            exec('ul_{} ={}'.format(row['mark'], row['UL']))
            exec('ll_{} ={}'.format(row['mark'], row['LL']))
        # 分配比例上下限，按照性状的
        for i in range(0, len(var_list)):
            print(var_list[i])
            tmp_var = var_list[i]
            data_bili_2 = data_bili[(data_bili['FLAG'] == '性状') & (data_bili['VAR'] == tmp_var)]
            data_bili_2 = data_bili_2.reset_index(drop=True)
            tmp_ul = data_bili_2.loc[0]['UL']
            tmp_ll = data_bili_2.loc[0]['LL']
            exec('ul_var_{} ={}'.format(i + 1, tmp_ul))
            exec('ll_var_{} ={}'.format(i + 1, tmp_ll))
        # 分配比例上下限，按照来源的
        for i in range(0, len(source_list)):
            print(source_list[i])
            tmp_source = source_list[i]
            data_bili_3 = data_bili[(data_bili['FLAG'] == '来源') & (data_bili['SOURCE'] == tmp_source)]
            data_bili_3 = data_bili_3.reset_index(drop=True)
            tmp_ul = data_bili_3.loc[0]['UL']
            tmp_ll = data_bili_3.loc[0]['LL']
            exec('ul_source_{} ={}'.format(i + 1, tmp_ul))
            exec('ll_source_{} ={}'.format(i + 1, tmp_ll))
        # 分配比例上下限，按照性状来源的

        # var_df_1 = var_df[(var_df['count'] > 0)]
        # var_df_1 = var_df_1.reset_index(drop=True)

        var_df1 = var_df.copy()

        def __cal_new_var(x):
            rst = str(x.VAR) + '_' + str(x.SOURCE)
            return rst

        var_df1['NEW_VAR'] = var_df1.apply(lambda x: __cal_new_var(x), axis=1)

        data_bili_4 = data_bili[(data_bili['FLAG'] == '性状来源')]
        data_bili_4 = data_bili_4.reset_index(drop=True)

        def __cal_new_var(x):
            rst = str(x.VAR) + '_' + str(x.SOURCE)
            return rst

        data_bili_4['NEW_VAR'] = data_bili_4.apply(lambda x: __cal_new_var(x), axis=1)
        data_bili_4 = data_bili_4[['NEW_VAR', 'UL', 'LL']]
        v = ['NEW_VAR']
        df3 = pd.merge(var_df1, data_bili_4, on=v, how='left')
        for index, row in df3.iterrows():
            exec('ul_group_{} ={}'.format(row['group'], row['UL']))
            exec('ll_group_{} ={}'.format(row['group'], row['LL']))
        data_bili_5 = data_bili[(data_bili['FLAG'] == '特殊')]
        data_bili_5 = data_bili_5.reset_index(drop=True)
        teshu_ul = data_bili_5.loc[0]['UL']
        teshu_ll = data_bili_5.loc[0]['LL']
        # 煤质
        data_meizhi.rename(columns={'H2O': 'shui'}, inplace=True)
        data_meizhi.rename(columns={'ASH': 'hui'}, inplace=True)
        data_meizhi.rename(columns={'COKE_VM': 'huifa'}, inplace=True)
        data_meizhi.rename(columns={'S': 'liu'}, inplace=True)
        data_meizhi.rename(columns={'COKING_COALBLD_BOND_IND': 'gzhi'}, inplace=True)
        data_meizhi.rename(columns={'M_L3_JZCHD_Y': 'yzhi'}, inplace=True)
        data_meizhi.rename(columns={'COKING_COALBLD_GIESFLU': 'liudong'}, inplace=True)
        data_meizhi.rename(columns={'L_M_AB': 'pengzhang'}, inplace=True)
        data_meizhi.rename(columns={'C': 'cc'}, inplace=True)
        data_meizhi.rename(columns={'COKE_HOTVALUE': 're'}, inplace=True)
        data_meizhi_1 = data_meizhi[
            ['PROD_CODE', 'shui', 'hui', 'huifa', 'liu', 'gzhi', 'yzhi', 'liudong', 'pengzhang', 'cc', 're']]
        v = ['PROD_CODE']
        df3 = pd.merge(data_mark, data_meizhi_1, on=v, how='left')
        df3.fillna(max_constant, inplace=True)
        for index, row in df3.iterrows():
            exec('shui_{} ={}'.format(row['mark'], row['shui']))
            exec('hui_{} ={}'.format(row['mark'], row['hui']))
            exec('huifa_{} ={}'.format(row['mark'], row['huifa']))
            exec('liu_{} ={}'.format(row['mark'], row['liu']))
            exec('gzhi_{} ={}'.format(row['mark'], row['gzhi']))
            exec('yzhi_{} ={}'.format(row['mark'], row['yzhi']))
            exec('liudong_{} ={}'.format(row['mark'], row['liudong']))
            exec('pengzhang_{} ={}'.format(row['mark'], row['pengzhang']))
            exec('cc_{} ={}'.format(row['mark'], row['cc']))
            exec('re_{} ={}'.format(row['mark'], row['re']))
        # 煤质上下限
        data_shangxiaxian_1 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '上限') & (data_shangxiaxian['BIG_VAR'] == '炼焦煤')]
        data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
        data_shangxiaxian_1.fillna(max_constant, inplace=True)
        lianjiao_shui_ul = data_shangxiaxian_1.loc[0]['H2O']
        lianjiao_hui_ul = data_shangxiaxian_1.loc[0]['ASH']
        lianjiao_huifa_ul = data_shangxiaxian_1.loc[0]['COKE_VM']
        lianjiao_liu_ul = data_shangxiaxian_1.loc[0]['S']
        lianjiao_gzhi_ul = data_shangxiaxian_1.loc[0]['COKING_COALBLD_BOND_IND']
        lianjiao_yzhi_ul = data_shangxiaxian_1.loc[0]['M_L3_JZCHD_Y']
        lianjiao_liudong_ul = data_shangxiaxian_1.loc[0]['COKING_COALBLD_GIESFLU']
        lianjiao_pengzhang_ul = data_shangxiaxian_1.loc[0]['L_M_AB']
        lianjiao_cc_ul = data_shangxiaxian_1.loc[0]['C']
        lianjiao_re_ul = data_shangxiaxian_1.loc[0]['COKE_HOTVALUE']
        data_shangxiaxian_2 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '下限') & (data_shangxiaxian['BIG_VAR'] == '炼焦煤')]
        data_shangxiaxian_2 = data_shangxiaxian_2.reset_index(drop=True)
        data_shangxiaxian_2.fillna(0, inplace=True)
        lianjiao_shui_ll = data_shangxiaxian_2.loc[0]['H2O']
        lianjiao_hui_ll = data_shangxiaxian_2.loc[0]['ASH']
        lianjiao_huifa_ll = data_shangxiaxian_2.loc[0]['COKE_VM']
        lianjiao_liu_ll = data_shangxiaxian_2.loc[0]['S']
        lianjiao_gzhi_ll = data_shangxiaxian_2.loc[0]['COKING_COALBLD_BOND_IND']
        lianjiao_yzhi_ll = data_shangxiaxian_2.loc[0]['M_L3_JZCHD_Y']
        lianjiao_liudong_ll = data_shangxiaxian_2.loc[0]['COKING_COALBLD_GIESFLU']
        lianjiao_pengzhang_ll = data_shangxiaxian_2.loc[0]['L_M_AB']
        lianjiao_cc_ll = data_shangxiaxian_2.loc[0]['C']
        lianjiao_re_ll = data_shangxiaxian_2.loc[0]['COKE_HOTVALUE']
        # 品种价格
        df3 = data_mark.copy()
        data_price_1 = data_meizhong[['PROD_CODE', 'UNIT_PRICE']]
        # v = ['PROD_CODE']
        # df3 = pd.merge(data_mark, data_price_1, on=v, how='left')
        df3.fillna(max_constant, inplace=True)
        for index, row in df3.iterrows():
            exec('price_{} ={}'.format(row['mark'], row['UNIT_PRICE']))
        # 其他配置的参数(灰中成分，煤岩分析，焦炭参数)
        # sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,FE2O3,SIO2,CAO,AL2O3,MGO,FOP_TIO2, " \
        #       " MNO,P2O5,BF_SINTER_QLTY_S,ZN,PB,FOP_K2O,FOP_NA2O,VITRI_REFL,VITRI_COAL_OI " \
        # 灰中成分
        data_huizhongchengfen.rename(columns={'FE2O3': 'hui_FE2O3'}, inplace=True)
        data_huizhongchengfen.rename(columns={'SIO2': 'hui_SIO2'}, inplace=True)
        data_huizhongchengfen.rename(columns={'CAO': 'hui_CAO'}, inplace=True)
        data_huizhongchengfen.rename(columns={'AL2O3': 'hui_AL2O3'}, inplace=True)
        data_huizhongchengfen.rename(columns={'MGO': 'hui_MGO'}, inplace=True)
        data_huizhongchengfen.rename(columns={'FOP_TIO2': 'hui_TIO2'}, inplace=True)
        data_huizhongchengfen.rename(columns={'MNO': 'hui_MNO'}, inplace=True)
        data_huizhongchengfen.rename(columns={'P2O5': 'hui_P2O5'}, inplace=True)
        data_huizhongchengfen.rename(columns={'BF_SINTER_QLTY_S': 'hui_S'}, inplace=True)
        data_huizhongchengfen.rename(columns={'ZN': 'hui_ZN'}, inplace=True)
        data_huizhongchengfen.rename(columns={'PB': 'hui_PB'}, inplace=True)
        data_huizhongchengfen.rename(columns={'FOP_K2O': 'hui_K2O'}, inplace=True)
        data_huizhongchengfen.rename(columns={'FOP_NA2O': 'hui_NA2O'}, inplace=True)
        data_huizhongchengfen.rename(columns={'VITRI_REFL': 'meiyan_R'}, inplace=True)
        data_huizhongchengfen.rename(columns={'VITRI_COAL_OI': 'meiyan_Ti'}, inplace=True)
        data_huizhongchengfen_1 = data_huizhongchengfen[
            ['PROD_CODE', 'hui_FE2O3', 'hui_SIO2', 'hui_CAO', 'hui_AL2O3', 'hui_MGO', 'hui_TIO2', 'hui_MNO', 'hui_P2O5',
             'hui_S', 'hui_ZN', 'hui_PB', 'hui_K2O', 'hui_NA2O', 'meiyan_R', 'meiyan_Ti']]
        v = ['PROD_CODE']
        df3 = pd.merge(data_mark, data_huizhongchengfen_1, on=v, how='left')
        df3.fillna(max_constant, inplace=True)
        for index, row in df3.iterrows():
            exec('hui_FE2O3_{} ={}'.format(row['mark'], row['hui_FE2O3']))
            exec('hui_SIO2_{} ={}'.format(row['mark'], row['hui_SIO2']))
            exec('hui_CAO_{} ={}'.format(row['mark'], row['hui_CAO']))
            exec('hui_AL2O3_{} ={}'.format(row['mark'], row['hui_AL2O3']))
            exec('hui_MGO_{} ={}'.format(row['mark'], row['hui_MGO']))
            exec('hui_TIO2_{} ={}'.format(row['mark'], row['hui_TIO2']))
            exec('hui_MNO_{} ={}'.format(row['mark'], row['hui_MNO']))
            exec('hui_P2O5_{} ={}'.format(row['mark'], row['hui_P2O5']))
            exec('hui_S_{} ={}'.format(row['mark'], row['hui_S']))
            exec('hui_ZN_{} ={}'.format(row['mark'], row['hui_ZN']))
            exec('hui_PB_{} ={}'.format(row['mark'], row['hui_PB']))
            exec('hui_K2O_{} ={}'.format(row['mark'], row['hui_K2O']))
            exec('hui_NA2O_{} ={}'.format(row['mark'], row['hui_NA2O']))
            exec('meiyan_R_{} ={}'.format(row['mark'], row['meiyan_R']))
            exec('meitan_Ti_{} ={}'.format(row['mark'], row['meiyan_Ti']))

        # 构建系数矩阵
        coef_df = data_mark[['mark']]
        mark_list = coef_df['mark'].to_list()
        array_len = len(mark_list)
        # 构建所有参数对应的约束df
        canshu_df = pd.DataFrame(
            columns=['index', 'coef_name', 'step', 'mark', 'vvar', 'source', 'group', 'meizhi', 'merge_flag'])
        dict_canshu = {}
        j_start = 0
        # 炼焦煤总量,暂认为干煤量
        # 等式约束
        import numpy as np
        from numpy import array
        y0 = array([[0] * array_len], dtype=float)
        y0[0, 0] = 1
        mark_tmp_0 = mark_list[0]
        ldict100 = {}
        exec('m0 = array([ziyuanliang_{}])'.format(mark_tmp_0), locals(), ldict100)
        m0 = ldict100["m0"]
        #默认第一个约束条件是通的
        # if mark_tmp_0[0] == '1':
        #     buzhou_tmp = '主焦'
        # elif mark_tmp_0[0] == '2':
        #     buzhou_tmp = '肥煤'
        # elif mark_tmp_0[0] == '3':
        #     buzhou_tmp = '1/3焦'
        # elif mark_tmp_0[0] == '4':
        #     buzhou_tmp = '气煤'
        # elif mark_tmp_0[0] == '5':
        #     buzhou_tmp = '瘦煤'
        # dict_canshu['index'] = 0
        # dict_canshu['coef_name'] = '资源量'
        # dict_canshu['step'] = '确定煤种以及资源量'
        # dict_canshu['mark'] = mark_tmp_0
        # new_row = pd.Series(dict_canshu)
        # canshu_df = canshu_df.append(new_row, ignore_index=True)
        # dict_canshu = {}

        j_start = j_start + 1
        e2 = array([[1] * array_len], dtype=float)
        f2 = array([peihemei_wt])
        # 不等式约束
        # 资源量
        # 品名资源量
        for j in range(j_start, j_start + int(array_len)-1):
            exec('y{} = array([[0] * array_len],dtype=float)'.format(j))
            exec('y{}[0,j-j_start+1] = 1'.format(j))
            mark_tmp = mark_list[j]
            exec('m{} = array([ziyuanliang_{}])'.format(j, mark_tmp))
            dict_canshu['index'] = j
            dict_canshu['coef_name'] = '资源量'
            if mark_tmp[0] == '1':
                buzhou_tmp = '主焦'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '肥煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '1/3焦'
            elif mark_tmp[0] == '4':
                buzhou_tmp = '气煤'
            elif mark_tmp[0] == '5':
                buzhou_tmp = '瘦煤'
            dict_canshu['step'] = '确定煤种以及资源量'
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
        j_start = j_start + int(array_len)-1
        # 合并单元格资源量

        for j in range(j_start, int(merge_num) + j_start):
            print(j)
            exec('y{} = array([[0] * array_len],dtype=float)'.format(j))
            data_mark_merge = data_mark[(data_mark['MERGE_FLAG'] == j - j_start + 1)]
            data_mark_merge = data_mark_merge.reset_index(drop=True)
            var_tmp = data_mark_merge.loc[0]['VAR']

            data_mark_merge['if_merge'] = 1
            yuechukucun_sum = data_mark_merge['INIT_INV'].sum()
            data_mark_merge = data_mark_merge[['mark', 'if_merge']]
            v = ['mark']
            df3_merge = pd.merge(coef_df, data_mark_merge, on=v, how='left')
            df3_merge.if_merge.fillna(0, inplace=True)
            for index, row in df3_merge.iterrows():
                exec('y{}[0,index] = row["if_merge"]'.format(j))
            exec('m{} = array([merge_ziyuanliang_{}+yuechukucun_sum])'.format(j, j - j_start + 1))

            dict_canshu['index'] = j
            dict_canshu['coef_name'] = '合并可供资源量'
            dict_canshu['step'] = '确定煤种以及资源量'
            dict_canshu['merge_flag'] = j - j_start + 1
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}

        j_start = j_start + int(merge_num)
        # 分配比例
        # 性状分配比例
        ldict1 = {}
        ldict2 = {}
        for i in range(1, len(var_list) + 1):
            var_tmp = str(i)
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_var_{}'.format(var_tmp), locals(), ldict1)
            true_value = ldict1["true_value"]
            # print(true_value)
            exec('false_value = - ul_var_{}'.format(var_tmp), locals(), ldict1)
            false_value = ldict1["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0] == var_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状比例上限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['vvar'] = var_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_var_{} - 100'.format(var_tmp), locals(), ldict2)
            true_value = ldict2["true_value"]
            # print(true_value)
            exec('false_value = ll_var_{}'.format(var_tmp), locals(), ldict2)
            false_value = ldict2["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0] == var_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状比例下限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['vvar'] = var_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 特殊，主焦+肥煤
        # 上限
        coef_df1 = coef_df.copy()
        true_value = 100 - teshu_ul
        false_value = - teshu_ul
        # print(false_value)
        def __cal_coef(x):
            print(x.mark[0])
            if x.mark[0] == '1' or x.mark[0] == '2':
                rst = true_value
            else:
                rst = false_value
            return rst

        coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
        exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
        for index, row in coef_df1.iterrows():
            exec("y{}[0, index] = row['coef']".format(j_start))
        exec("m{} =  array([0])".format(j_start))
        dict_canshu['index'] = j_start
        dict_canshu['coef_name'] = '特殊比例上限'
        dict_canshu['step'] = '维度比例上下限'
        new_row = pd.Series(dict_canshu)
        canshu_df = canshu_df.append(new_row, ignore_index=True)
        dict_canshu = {}
        j_start = j_start + 1
        # 下限
        coef_df1 = coef_df.copy()
        true_value = teshu_ll - 100
        false_value = teshu_ll
        def __cal_coef(x):
            if x.mark[0] == '1' or x.mark[0] == '2':
                rst = true_value
            else:
                rst = false_value
            return rst

        coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
        exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
        for index, row in coef_df1.iterrows():
            exec("y{}[0, index] = row['coef']".format(j_start))
        exec("m{} =  array([0])".format(j_start))
        dict_canshu['index'] = j_start
        dict_canshu['coef_name'] = '特殊比例下限'
        dict_canshu['step'] = '维度比例上下限'
        new_row = pd.Series(dict_canshu)
        canshu_df = canshu_df.append(new_row, ignore_index=True)
        dict_canshu = {}
        j_start = j_start + 1

        # 来源分配比例
        ldict3 = {}
        ldict4 = {}
        for i in range(1, len(source_list) + 1):
            source_tmp = str(i)
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_source_{}'.format(source_tmp), locals(), ldict3)
            true_value = ldict3["true_value"]
            # print(true_value)
            exec('false_value = - ul_source_{}'.format(source_tmp), locals(), ldict3)
            false_value = ldict3["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[2] == source_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '来源比例上限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['source'] = source_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_source_{} - 100'.format(source_tmp), locals(), ldict4)
            true_value = ldict4["true_value"]
            # print(true_value)
            exec('false_value = ll_source_{}'.format(source_tmp), locals(), ldict4)
            false_value = ldict4["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[2] == source_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '来源比例下限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['source'] = source_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 性状来源分配比例
        group_list = var_df['group'].to_list()
        ldict5 = {}
        ldict6 = {}
        for group_tmp in group_list:
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_group_{}'.format(group_tmp), locals(), ldict5)
            true_value = ldict5["true_value"]
            # print(true_value)
            exec('false_value = - ul_group_{}'.format(group_tmp), locals(), ldict5)
            false_value = ldict5["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0:3] == group_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状来源比例上限'
            dict_canshu['step'] = '维度比例上下限'
            if group_tmp[0] == '1':
                var_tmp = '主焦'
            elif group_tmp[0] == '2':
                var_tmp = '肥煤'
            elif group_tmp[0] == '3':
                var_tmp = '1/3焦'
            elif group_tmp[0] == '4':
                var_tmp = '气煤'
            elif group_tmp[0] == '5':
                var_tmp = '瘦煤'
            dict_canshu['vvar'] = var_tmp
            if group_tmp[2] == '1':
                source_tmp = '进口'
            elif group_tmp[2] == '2':
                source_tmp = '大矿长协'
            elif group_tmp[2] == '3':
                source_tmp = '大矿市场'
            elif group_tmp[2] == '4':
                source_tmp = '地方矿'
            dict_canshu['source'] = source_tmp
            dict_canshu['group'] = group_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_group_{} - 100'.format(group_tmp), locals(), ldict6)
            true_value = ldict6["true_value"]
            # print(true_value)
            exec('false_value = ll_group_{}'.format(group_tmp), locals(), ldict6)
            false_value = ldict6["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0:3] == group_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状来源比例下限'
            dict_canshu['step'] = '维度比例上下限'
            if group_tmp[0] == '1':
                var_tmp = '主焦'
            elif group_tmp[0] == '2':
                var_tmp = '肥煤'
            elif group_tmp[0] == '3':
                var_tmp = '1/3焦'
            elif group_tmp[0] == '4':
                var_tmp = '气煤'
            elif group_tmp[0] == '5':
                var_tmp = '瘦煤'
            dict_canshu['vvar'] = var_tmp
            if group_tmp[2] == '1':
                source_tmp = '进口'
            elif group_tmp[2] == '2':
                source_tmp = '大矿长协'
            elif group_tmp[2] == '3':
                source_tmp = '大矿市场'
            elif group_tmp[2] == '4':
                source_tmp = '地方矿'
            dict_canshu['source'] = source_tmp
            dict_canshu['group'] = group_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 品名分配比例
        ldict7 = {}
        ldict8 = {}
        for mark_tmp in mark_list:
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_{}'.format(mark_tmp), locals(), ldict7)
            true_value = ldict7["true_value"]
            # print(true_value)
            exec('false_value = - ul_{}'.format(mark_tmp), locals(), ldict7)
            false_value = ldict7["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark == mark_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '品种比例上限'
            if mark_tmp[0] == '1':
                buzhou_tmp = '主焦'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '肥煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '1/3焦'
            elif mark_tmp[0] == '4':
                buzhou_tmp = '气煤'
            elif mark_tmp[0] == '5':
                buzhou_tmp = '瘦煤'
            dict_canshu['step'] = buzhou_tmp
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_{} - 100'.format(mark_tmp), locals(), ldict8)
            true_value = ldict8["true_value"]
            # print(true_value)
            exec('false_value = ll_{}'.format(mark_tmp), locals(), ldict8)
            false_value = ldict8["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark == mark_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '品种比例下限'
            if mark_tmp[0] == '1':
                buzhou_tmp = '主焦'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '肥煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '1/3焦'
            elif mark_tmp[0] == '4':
                buzhou_tmp = '气煤'
            elif mark_tmp[0] == '5':
                buzhou_tmp = '瘦煤'
            dict_canshu['step'] = buzhou_tmp
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 煤质
        meizhi_list = ['shui', 'hui', 'huifa', 'liu', 'gzhi', 'yzhi', 'liudong', 'pengzhang', 'cc', 're']
        big_var_tmp = 'lianjiao'
        for meizhi_tmp in meizhi_list:
            coef_df1 = coef_df.copy()
            # 上限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                exec("y{}[0, index] = {}_{} - {}_{}_ul".format(j_start, meizhi_tmp, mark_tmp, big_var_tmp,
                                                               meizhi_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '上限'
            dict_canshu['step'] = '煤质上下限'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                exec("y{}[0, index] = {}_{}_ll - {}_{}".format(j_start, big_var_tmp, meizhi_tmp, meizhi_tmp,
                                                               mark_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '下限'
            dict_canshu['step'] = '煤质上下限'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1

        # 拼接
        ldict0 = {}
        y_str = 'y0'
        # yyy = (y0,y0)
        for j in range(1, j_start):
            y_str = y_str + ',' + 'y' + str(j)
        # exec('yyy =({})'.format(y_str))
        exec('yyy =({})'.format(y_str), locals(), ldict0)
        yyy = ldict0["yyy"]
        Y = np.concatenate(yyy, axis=0)

        m_str = 'm0'
        # mmm = (m0,m0)
        for j in range(1, j_start):
            m_str = m_str + ',' + 'm' + str(j)
        # exec('mmm =({})'.format(m_str))
        exec('mmm =({})'.format(m_str), locals(), ldict0)
        mmm = ldict0["mmm"]
        M = np.concatenate(mmm, axis=0)
        # print('finish')
        # 价格
        # cc价格
        # cc = array([[1] * array_len], dtype=float)
        cc_str = ''
        ldict10 = {}
        for i in range(0, len(mark_list)):
            mark_tmp = mark_list[i]
            if i == 0:
                exec("cc_str = cc_str + 'price_{}'".format(mark_tmp), locals(), ldict10)
                cc_str = ldict10["cc_str"]
            else:
                exec("cc_str = cc_str + ',' + 'price_{}'".format(mark_tmp), locals(), ldict10)
                cc_str = ldict10["cc_str"]
                # print(cc_str)
        exec("cc = array([{}])".format(cc_str), locals(), ldict0)
        cc = ldict0["cc"]



        def cal_total_cost(x):
            total_cost = cc @ x

            return total_cost

        e1 = Y
        f1 = M

        x = cp.Variable(array_len)
        print(type(x))
        obj = cp.Minimize(cal_total_cost(x))
        cons = [e1 @ x <= f1, e2 @ x == f2, x >= 0]
        prob = cp.Problem(obj, cons)
        prob.solve(solver='GLPK_MI', verbose=True)
        print("最优初始值为:", prob.value)
        print("最优初始解为：\n", x.value)
        success = 0
        if x.value is None:
            success = 0
            message = '求不出最优方案'
            return message
        x_value_tmp = x.value
        chushi_z = cal_total_cost(abs(x_value_tmp))


        success = 1
        for i in range(0, array_len):
            mark_tmp = mark_list[i]
            value_tmp = abs(x_value_tmp[i])
            exec("chushi_x_{} = {}".format(mark_tmp, value_tmp))

        ###粒子初始解构造
        coef_df1 = coef_df.copy()
        # coef_df1['chushi'] = x.value
        coef_df1['chushi'] = abs(x_value_tmp)
        # 存在等式约束，PSO处理等式容易出界，舍掉一个变量用其他变量去表示出来
        coef_df1 = coef_df1.reset_index(drop=True)
        chushi_sum = coef_df1['chushi'].sum()
        print(chushi_sum)
        print(peihemei_wt)

        first_mark = coef_df1.loc[0]['mark']
        pso_coef_df1 = coef_df1[(coef_df1['mark'] != first_mark)]
        pso_coef_df1 = pso_coef_df1.reset_index(drop=True)

        pso_mark_list = pso_coef_df1['mark'].to_list()
        len_pso_mark = len(pso_mark_list)

        dim = len_pso_mark

        def build_chushi(chushi_n, chushi_value_tmp):
            for i in range(0, array_len):
                mark_tmp = mark_list[i]
                coef_df2 = coef_df1[coef_df1['mark'] == mark_tmp]
                coef_df2 = coef_df2.reset_index(drop=True)
                chushi_value = coef_df2.loc[0]['chushi']
                if int(chushi_value_tmp) != int(chushi_value):
                    chushi_value_tmp = chushi_value_tmp
                    # chushi_value_tmp = int(chushi_value_tmp) + diff
                    e24 = array([[0] * array_len], dtype=float)
                    e24[0, i] = 1
                    e22 = (e2, e24)
                    e222 = np.concatenate(e22, axis=0)
                    f222 = array([peihemei_wt, chushi_value_tmp])
                    x = cp.Variable(array_len)
                    obj = cp.Minimize(cal_total_cost(x))
                    cons = [e1 @ x <= f1, e222 @ x == f222, x >= 0]
                    prob = cp.Problem(obj, cons)
                    prob.solve(solver='GLPK_MI', verbose=True)
                    success = 0
                    if x.value is None:
                        success = 0
                    else:
                        success = 1
                        chushi_n = chushi_n + 1
                        coef_df1[str(chushi_n)] = abs(x.value)
            return chushi_n

        def build_chushi2(chushi_n, diff):
            for i in range(0, array_len):
                mark_tmp = mark_list[i]
                coef_df2 = coef_df1[coef_df1['mark'] == mark_tmp]
                coef_df2 = coef_df2.reset_index(drop=True)
                chushi_value_tmp = coef_df2.loc[0]['chushi']
                chushi_value_tmp = int(chushi_value_tmp) + diff
                if chushi_value_tmp >= 0:
                    e24 = array([[0] * array_len], dtype=float)
                    e24[0, i] = 1
                    e22 = (e2, e24)
                    e222 = np.concatenate(e22, axis=0)
                    f222 = array([peihemei_wt, chushi_value_tmp])
                    x = cp.Variable(array_len)
                    obj = cp.Minimize(cal_total_cost(x))
                    cons = [e1 @ x <= f1, e222 @ x == f222, x >= 0]
                    prob = cp.Problem(obj, cons)
                    prob.solve(solver='GLPK_MI', verbose=True)
                    success = 0
                    if x.value is None:
                        success = 0
                    else:
                        success = 1
                        chushi_n = chushi_n + 1
                        coef_df1[str(chushi_n)] = abs(x.value)
            return chushi_n

        chushi_n = 0
        # 置0初始解
        # for i in range(0, 20):
        #     chushi_value_tmp = i
        #     chushi_n = build_chushi(chushi_n, chushi_value_tmp)
        for i in range(1, 20):
            chushi_n = build_chushi2(chushi_n, i * 0.1)
            chushi_n = build_chushi2(chushi_n, -i * 0.1)
            if chushi_n >= 3 * size:
                break
        max_contant2 = peihemei_wt
        low = [0] * len_pso_mark
        up = [max_constant] * len_pso_mark
        for i in range(0, len_pso_mark):
            pso_mark_tmp = pso_mark_list[i]
            exec('up[i] = min(max_constant,ziyuanliang_{})'.format(pso_mark_tmp))
        # print(up)
        bound = []  # 变量的约束范围
        bound.append(low)
        bound.append(up)
        coef_df1_1 = coef_df1[coef_df1['mark'] != first_mark]
        coef_df1_1 = coef_df1_1.reset_index(drop=True)
        coef_df1_1.drop(['mark'], axis=1, inplace=True)
        coef_df1_1.drop(['chushi'], axis=1, inplace=True)

        X_df = coef_df1_1.T
        X_df_sample = X_df.sample(n=size)
        X_df_sample = X_df_sample.reset_index(drop=True)

        XNd = X_df_sample.values
        # 初始化种群的各个粒子的速度
        V = np.random.uniform(-max_vel, max_vel, size=(size, dim))
        # pso_cc价格
        pso_cc_str = ''
        ldict11 = {}
        for i in range(0, len(pso_mark_list)):
            pso_mark_tmp = pso_mark_list[i]
            if i == 0:
                exec("pso_cc_str = pso_cc_str + 'price_{}'".format(pso_mark_tmp), locals(), ldict11)
                pso_cc_str = ldict11["pso_cc_str"]
            else:
                exec("pso_cc_str = pso_cc_str + ',' + 'price_{}'".format(pso_mark_tmp), locals(), ldict11)
                pso_cc_str = ldict11["pso_cc_str"]
                # print(cc_str)
        exec("pso_cc = array([{}])".format(pso_cc_str), locals(), ldict0)
        pso_cc = ldict0["pso_cc"]

        # print(type(XNd[0]))
        # print(XNd[0])
        # print(XNd[0].shape)
        # tmp_x = XNd[0]
        # print(tmp_x.shape)
        # sum_all_tmp = np.sum(tmp_x)
        # print(sum_all_tmp)
        # first_mark_wt_tmp = abs(peihemei_wt - sum_all_tmp)
        # tmp_x_new = np.insert(tmp_x,0,first_mark_wt_tmp,axis=0)
        # print(tmp_x_new.shape)
        #
        # tmp_x_new_T = tmp_x_new.T
        # print(tmp_x_new_T.shape)
        # TMP_z = cal_total_cost(abs(tmp_x_new_T))
        # TMP_z2 = cal_total_cost(abs(tmp_x_new))
        # print('finish')
        def calc_f(X):
            x_tmp = X
            sum_x_tmp = np.sum(x_tmp)
            first_mark_wt_tmp = abs(peihemei_wt - sum_x_tmp)
            new_x_tmp = np.insert(x_tmp, 0, first_mark_wt_tmp, axis=0)
            # z_tmp = cal_total_cost(abs(new_x_tmp))
            x = abs(new_x_tmp)
            total_cost = cc @ x
            z_tmp = total_cost
            return z_tmp

        Z = calc_f(XNd[0])

        pso_coef_df2 = pso_coef_df1.copy()
        cf_str = 'peihemei_wt -  (0'
        for index, row in pso_coef_df2.iterrows():
            cf_str = cf_str + '+ x[' + str(index) + ']'
        cf_str = cf_str + ')'
        str1 = cf_str
        # 构建mark以及对应字符串
        mark_str_df = coef_df.copy()
        pso_mark_df = pso_coef_df1.copy()
        pso_mark_df.drop(['chushi'], axis=1, inplace=True)
        pso_mark_df = pso_mark_df.reset_index(drop=True)
        pso_mark_df = pso_mark_df.reset_index(drop=False)
        pso_mark_df.rename(columns={'index': 'rank'}, inplace=True)
        pso_mark_df['rank0'] = pso_mark_df['rank'].astype(int)
        pso_mark_df.drop(['rank'], axis=1, inplace=True)

        def __cal_coef(x):
            rst = 'x[' + str(x.rank0) + ']'
            return rst

        pso_mark_df['pso_str'] = pso_mark_df.apply(lambda x: __cal_coef(x), axis=1)
        pso_mark_df['pso'] = 1
        v = ['mark']
        mark_str_df = pd.merge(mark_str_df, pso_mark_df, on=v, how='left')

        def __cal_str(x):
            if x.mark == first_mark:
                rst = '(' + str1 + ')'
            else:
                rst = x.pso_str
            return rst

        mark_str_df['pso_str_final'] = mark_str_df.apply(lambda x: __cal_str(x), axis=1)
        for j in range(0, j_start):
            mark_str_df1 = mark_str_df.copy()
            exec("mark_str_df1['coef'] = y{}.T".format(j))
            exec("mark_str_df1['m'] = m{}[0]".format(j))

            cf_str = 'lambda x: '
            for index, row in mark_str_df1.iterrows():
                cf_str = cf_str + '+' + str(row['coef']) + '*' + str(row['pso_str_final']) + '-' + str(row['m'])
            # print(j)
            exec("cf{}_str = cf_str".format(j))
            # k_start = k_start + 1
        constraint_ueq_str = '('
        ldict12 = {}
        for k in range(0, j_start):
            # exec("constraint_ueq_str = constraint_ueq_str + cf{}_str + ','".format(k))
            exec("constraint_ueq_str = constraint_ueq_str + cf{}_str + ','".format(k), locals(), ldict12)
            constraint_ueq_str = ldict12["constraint_ueq_str"]
            # print(constraint_ueq_str)
        # constraint_ueq = (lambda x: -x[0],)
        constraint_ueq_str = constraint_ueq_str + ')'
        # exec("constraint_ueq = {}".format(constraint_ueq_str))
        exec("constraint_ueq = {}".format(constraint_ueq_str), locals(), ldict0)
        constraint_ueq = ldict0["constraint_ueq"]

        X_chushi = pso_coef_df1['chushi']

        def calc_f2(X, scheme_name):
            x_tmp = X
            sum_x_tmp = np.sum(x_tmp)
            first_mark_wt_tmp = abs(peihemei_wt - sum_x_tmp)
            s2 = pd.Series([first_mark_wt_tmp])

            sx = pd.concat([s2, x_tmp])
            x = abs(sx)
            x = x.reset_index(drop=True)
            data_mark_out = data_mark.copy()
            data_mark_out['wt'] = x

            def __cal_new_wt(x):
                if x.wt < 0.01:
                    rst = 0
                else:
                    rst = x.wt
                return rst

            data_mark_out['new_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            data_mark_out.new_wt.fillna(0, inplace=True)
            data_mark_out.drop(['wt'], axis=1, inplace=True)
            data_mark_out.rename(columns={'new_wt': 'WT'}, inplace=True)
            data_mark_out.drop(['index_old'], axis=1, inplace=True)
            data_mark_out.drop(['INIT_INV'], axis=1, inplace=True)
            data_mark_out.drop(['INV_WT'], axis=1, inplace=True)
            data_mark_out.drop(['MERGE_FLAG'], axis=1, inplace=True)
            data_mark_out.drop(['MAX_WT'], axis=1, inplace=True)
            data_mark_out.drop(['group'], axis=1, inplace=True)
            data_mark_out.drop(['xingzhuang_rank'], axis=1, inplace=True)
            data_mark_out.drop(['laiyuan_rank'], axis=1, inplace=True)
            data_mark_out.drop(['pinming_rank'], axis=1, inplace=True)
            v = ['PROD_CODE']
            # data_mark_out = pd.merge(data_mark_out, data_price_1, on=v, how='left')
            data_mark_out = pd.merge(data_mark_out, data_meizhi_1, on=v, how='left')
            data_mark_out['TOTAL_PRICE'] = data_mark_out['UNIT_PRICE'] * data_mark_out['WT']
            peihemei_wt_tmp = data_mark_out['WT'].sum()
            data_mark_out['RATIO'] = data_mark_out['WT'] / peihemei_wt_tmp
            data_mark_out['TOTAL_H2O'] = data_mark_out['shui'] * data_mark_out['WT']
            data_mark_out['TOTAL_ASH'] = data_mark_out['hui'] * data_mark_out['WT']
            data_mark_out['TOTAL_COKE_VM'] = data_mark_out['huifa'] * data_mark_out['WT']
            data_mark_out['TOTAL_S'] = data_mark_out['liu'] * data_mark_out['WT']
            data_mark_out['TOTAL_COKING_COALBLD_BOND_IND'] = data_mark_out['gzhi'] * data_mark_out['WT']
            data_mark_out['TOTAL_M_L3_JZCHD_Y'] = data_mark_out['yzhi'] * data_mark_out['WT']
            data_mark_out['TOTAL_COKING_COALBLD_GIESFLU'] = data_mark_out['liudong'] * data_mark_out['WT']
            data_mark_out['TOTAL_L_M_AB'] = data_mark_out['pengzhang'] * data_mark_out['WT']
            data_mark_out['TOTAL_C'] = data_mark_out['cc'] * data_mark_out['WT']
            data_mark_out['TOTAL_COKE_HOTVALUE'] = data_mark_out['re'] * data_mark_out['WT']
            def __cal_new_wt(x):
                if x.UNIT_PRICE > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['price_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.shui > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['shui_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['hui_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.huifa > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['huifa_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.liu > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['liu_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.gzhi > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['gzhi_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.yzhi > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['yzhi_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.liudong > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['liudong_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.pengzhang > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['pengzhang_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.cc > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['cc_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.re > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            data_mark_out['re_wt'] = data_mark_out.apply(lambda x: __cal_new_wt(x), axis=1)
            df_out1 = data_mark_out.copy()
            df_out1.drop(['mark'], axis=1, inplace=True)
            df_out1.drop(['TOTAL_H2O'], axis=1, inplace=True)
            df_out1.drop(['TOTAL_ASH'], axis=1, inplace=True)
            df_out1.drop(['TOTAL_COKE_VM'], axis=1, inplace=True)
            df_out1.drop(['TOTAL_S'], axis=1, inplace=True)
            df_out1.drop(['TOTAL_COKING_COALBLD_BOND_IND'], axis=1, inplace=True)
            df_out1.drop(['TOTAL_M_L3_JZCHD_Y'], axis=1, inplace=True)
            df_out1.drop(['TOTAL_COKING_COALBLD_GIESFLU'], axis=1, inplace=True)
            df_out1.drop(['TOTAL_L_M_AB'], axis=1, inplace=True)
            df_out1.drop(['TOTAL_C'], axis=1, inplace=True)
            df_out1.drop(['TOTAL_COKE_HOTVALUE'], axis=1, inplace=True)

            df_out1['FLAG'] = '明细'
            df_out1.rename(columns={'shui': 'H2O'}, inplace=True)
            df_out1.rename(columns={'hui': 'ASH'}, inplace=True)
            df_out1.rename(columns={'huifa': 'COKE_VM'}, inplace=True)
            df_out1.rename(columns={'liu': 'S'}, inplace=True)
            df_out1.rename(columns={'gzhi': 'COKING_COALBLD_BOND_IND'}, inplace=True)
            df_out1.rename(columns={'yzhi': 'M_L3_JZCHD_Y'}, inplace=True)
            df_out1.rename(columns={'liudong': 'COKING_COALBLD_GIESFLU'}, inplace=True)
            df_out1.rename(columns={'pengzhang': 'L_M_AB'}, inplace=True)
            df_out1.rename(columns={'cc': 'C'}, inplace=True)
            df_out1.rename(columns={'re': 'COKE_HOTVALUE'}, inplace=True)
            def cal_tongji(df_out1, tongji_df_tmp, flag_name, varsource):
                var1_df1 = tongji_df_tmp

                var1_sum_wt = var1_df1['WT'].sum()
                var1_sum_ratio = var1_df1['RATIO'].sum()
                var1_sum_price = var1_df1['TOTAL_PRICE'].sum()
                var1_sum_shui = var1_df1['TOTAL_H2O'].sum()
                var1_sum_hui = var1_df1['TOTAL_ASH'].sum()
                var1_sum_huifa = var1_df1['TOTAL_COKE_VM'].sum()
                var1_sum_liu = var1_df1['TOTAL_S'].sum()
                var1_sum_gzhi = var1_df1['TOTAL_COKING_COALBLD_BOND_IND'].sum()
                var1_sum_yzhi = var1_df1['TOTAL_M_L3_JZCHD_Y'].sum()
                var1_sum_liudong = var1_df1['TOTAL_COKING_COALBLD_GIESFLU'].sum()
                var1_sum_pengzhang = var1_df1['TOTAL_L_M_AB'].sum()
                var1_sum_cc = var1_df1['TOTAL_C'].sum()
                var1_sum_re = var1_df1['TOTAL_COKE_HOTVALUE'].sum()
                var1_sum_price_wt = var1_df1['price_wt'].sum()
                var1_sum_shui_wt = var1_df1['shui_wt'].sum()
                var1_sum_hui_wt = var1_df1['hui_wt'].sum()
                var1_sum_huifa_wt = var1_df1['huifa_wt'].sum()
                var1_sum_liu_wt = var1_df1['liu_wt'].sum()
                var1_sum_gzhi_wt = var1_df1['gzhi_wt'].sum()
                var1_sum_yzhi_wt = var1_df1['yzhi_wt'].sum()
                var1_sum_liudong_wt = var1_df1['liudong_wt'].sum()
                var1_sum_pengzhang_wt = var1_df1['pengzhang_wt'].sum()
                var1_sum_cc_wt = var1_df1['cc_wt'].sum()
                var1_sum_re_wt = var1_df1['re_wt'].sum()
                dict = {}
                dict['FLAG'] = flag_name
                if flag_name == '统计性状':
                    dict['VAR'] = varsource
                elif flag_name == '统计来源':
                    dict['SOURCE'] = varsource
                dict['WT'] = var1_sum_wt
                dict['RATIO'] = var1_sum_ratio
                dict['TOTAL_PRICE'] = var1_sum_price
                if var1_sum_price_wt == 0:
                    dict['UNIT_PRICE'] = None
                else:
                    dict['UNIT_PRICE'] = var1_sum_price / var1_sum_price_wt
                if var1_sum_shui_wt == 0:
                    dict['H2O'] = None
                else:
                    dict['H2O'] = var1_sum_shui / var1_sum_shui_wt
                if var1_sum_hui_wt == 0:
                    dict['ASH'] = None
                else:
                    dict['ASH'] = var1_sum_hui / var1_sum_hui_wt
                if var1_sum_huifa_wt == 0:
                    dict['COKE_VM'] = None
                else:
                    dict['COKE_VM'] = var1_sum_huifa / var1_sum_huifa_wt
                if var1_sum_liu_wt == 0:
                    dict['S'] = None
                else:
                    dict['S'] = var1_sum_liu / var1_sum_liu_wt
                if var1_sum_gzhi_wt == 0:
                    dict['COKING_COALBLD_BOND_IND'] = None
                else:
                    dict['COKING_COALBLD_BOND_IND'] = var1_sum_gzhi / var1_sum_gzhi_wt
                if var1_sum_yzhi_wt == 0:
                    dict['M_L3_JZCHD_Y'] = None
                else:
                    dict['M_L3_JZCHD_Y'] = var1_sum_yzhi / var1_sum_yzhi_wt
                if var1_sum_liudong_wt == 0:
                    dict['COKING_COALBLD_GIESFLU'] = None
                else:
                    dict['COKING_COALBLD_GIESFLU'] = var1_sum_liudong / var1_sum_liudong_wt
                if var1_sum_pengzhang_wt == 0:
                    dict['L_M_AB'] = None
                else:
                    dict['L_M_AB'] = var1_sum_pengzhang / var1_sum_pengzhang_wt
                if var1_sum_cc_wt == 0:
                    dict['C'] = None
                else:
                    dict['C'] = var1_sum_cc / var1_sum_cc_wt
                if var1_sum_re_wt == 0:
                    dict['COKE_HOTVALUE'] = None
                else:
                    dict['COKE_HOTVALUE'] = var1_sum_re / var1_sum_re_wt
                new_row = pd.Series(dict)
                df_out1 = df_out1.append(new_row, ignore_index=True)

                return df_out1

            tongji_df_tmp = data_mark_out[(data_mark_out['VAR'] == '主焦')]
            df_out1 = cal_tongji(df_out1, tongji_df_tmp, '统计性状', '主焦')
            tongji_df_tmp = data_mark_out[(data_mark_out['VAR'] == '肥煤')]
            df_out1 = cal_tongji(df_out1, tongji_df_tmp, '统计性状', '肥煤')
            tongji_df_tmp = data_mark_out[(data_mark_out['VAR'] == '1/3焦')]
            df_out1 = cal_tongji(df_out1, tongji_df_tmp, '统计性状', '1/3焦')
            tongji_df_tmp = data_mark_out[(data_mark_out['VAR'] == '气煤')]
            df_out1 = cal_tongji(df_out1, tongji_df_tmp, '统计性状', '气煤')
            tongji_df_tmp = data_mark_out[(data_mark_out['VAR'] == '瘦煤')]
            df_out1 = cal_tongji(df_out1, tongji_df_tmp, '统计性状', '瘦煤')
            tongji_df_tmp = data_mark_out[(data_mark_out['SOURCE'] == '进口')]
            df_out1 = cal_tongji(df_out1, tongji_df_tmp, '统计来源', '进口')
            tongji_df_tmp = data_mark_out[(data_mark_out['SOURCE'] == '大矿长协')]
            df_out1 = cal_tongji(df_out1, tongji_df_tmp, '统计来源', '大矿长协')
            tongji_df_tmp = data_mark_out[(data_mark_out['SOURCE'] == '大矿市场')]
            df_out1 = cal_tongji(df_out1, tongji_df_tmp, '统计来源', '大矿市场')
            tongji_df_tmp = data_mark_out[(data_mark_out['SOURCE'] == '地方矿')]
            df_out1 = cal_tongji(df_out1, tongji_df_tmp, '统计来源', '地方矿')
            tongji_df_tmp = data_mark_out
            df_out1 = cal_tongji(df_out1, tongji_df_tmp, '统计配合煤', '')
            dict = {}
            dict['FLAG'] = '统计外购焦炭'
            dict['WT'] = WT_waigoujiaotan
            dict['TOTAL_PRICE'] = price_waigoujiaotan * WT_waigoujiaotan
            dict['UNIT_PRICE'] = price_waigoujiaotan
            new_row = pd.Series(dict)
            df_out1 = df_out1.append(new_row, ignore_index=True)
            # peihemei_total_price = cc @ x
            # peihemei_unit_price = peihemei_total_price / peihemei_wt
            df_out2_pre = data_mark_out.copy()
            v = ['PROD_CODE']
            df_out2_pre = pd.merge(df_out2_pre, data_huizhongchengfen_1, on=v, how='left')
            df_out2_pre['TOTAL_hui_FE2O3'] = df_out2_pre['hui_FE2O3'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_SIO2'] = df_out2_pre['hui_SIO2'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_CAO'] = df_out2_pre['hui_CAO'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_AL2O3'] = df_out2_pre['hui_AL2O3'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_MGO'] = df_out2_pre['hui_MGO'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_TIO2'] = df_out2_pre['hui_TIO2'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_MNO'] = df_out2_pre['hui_MNO'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_P2O5'] = df_out2_pre['hui_P2O5'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_S'] = df_out2_pre['hui_S'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_ZN'] = df_out2_pre['hui_ZN'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_PB'] = df_out2_pre['hui_PB'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_K2O'] = df_out2_pre['hui_K2O'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_hui_NA2O'] = df_out2_pre['hui_NA2O'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_meiyan_R'] = df_out2_pre['meiyan_R'] * df_out2_pre['WT']
            df_out2_pre['TOTAL_meiyan_Ti'] = df_out2_pre['meiyan_Ti'] * df_out2_pre['WT']
            def __cal_new_wt(x):
                if x.hui_FE2O3 > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_FE2O3_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_SIO2 > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_SIO2_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_CAO > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_CAO_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_AL2O3 > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_AL2O3_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_MGO > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_MGO_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_TIO2 > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_TIO2_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_MNO > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_MNO_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_P2O5 > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_P2O5_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_S > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_S_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_ZN > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_ZN_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_PB > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_PB_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_K2O > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_K2O_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.hui_NA2O > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['hui_NA2O_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.meiyan_R > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['meiyan_R_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            def __cal_new_wt(x):
                if x.meiyan_Ti > 0:
                    rst = x.WT
                else:
                    rst = 0
                return rst
            df_out2_pre['meiyan_Ti_wt'] = df_out2_pre.apply(lambda x: __cal_new_wt(x), axis=1)
            var1_sum_hui_FE2O3 = df_out2_pre['TOTAL_hui_FE2O3'].sum()
            var1_sum_hui_SIO2 = df_out2_pre['TOTAL_hui_SIO2'].sum()
            var1_sum_hui_CAO = df_out2_pre['TOTAL_hui_CAO'].sum()
            var1_sum_hui_AL2O3 = df_out2_pre['TOTAL_hui_AL2O3'].sum()
            var1_sum_hui_MGO = df_out2_pre['TOTAL_hui_MGO'].sum()
            var1_sum_hui_TIO2 = df_out2_pre['TOTAL_hui_TIO2'].sum()
            var1_sum_hui_MNO = df_out2_pre['TOTAL_hui_MNO'].sum()
            var1_sum_hui_P2O5 = df_out2_pre['TOTAL_hui_P2O5'].sum()
            var1_sum_hui_S = df_out2_pre['TOTAL_hui_S'].sum()
            var1_sum_hui_ZN = df_out2_pre['TOTAL_hui_ZN'].sum()
            var1_sum_hui_PB = df_out2_pre['TOTAL_hui_PB'].sum()
            var1_sum_hui_K2O = df_out2_pre['TOTAL_hui_K2O'].sum()
            var1_sum_hui_NA2O = df_out2_pre['TOTAL_hui_NA2O'].sum()
            var1_sum_meiyan_R = df_out2_pre['TOTAL_meiyan_R'].sum()
            var1_sum_meiyan_Ti = df_out2_pre['TOTAL_meiyan_Ti'].sum()
            var1_sum_hui_FE2O3_wt = df_out2_pre['hui_FE2O3_wt'].sum()
            var1_sum_hui_SIO2_wt = df_out2_pre['hui_SIO2_wt'].sum()
            var1_sum_hui_CAO_wt = df_out2_pre['hui_CAO_wt'].sum()
            var1_sum_hui_AL2O3_wt = df_out2_pre['hui_AL2O3_wt'].sum()
            var1_sum_hui_MGO_wt = df_out2_pre['hui_MGO_wt'].sum()
            var1_sum_hui_TIO2_wt = df_out2_pre['hui_TIO2_wt'].sum()
            var1_sum_hui_MNO_wt = df_out2_pre['hui_MNO_wt'].sum()
            var1_sum_hui_P2O5_wt = df_out2_pre['hui_P2O5_wt'].sum()
            var1_sum_hui_S_wt = df_out2_pre['hui_S_wt'].sum()
            var1_sum_hui_ZN_wt = df_out2_pre['hui_ZN_wt'].sum()
            var1_sum_hui_PB_wt = df_out2_pre['hui_PB_wt'].sum()
            var1_sum_hui_K2O_wt = df_out2_pre['hui_K2O_wt'].sum()
            var1_sum_hui_NA2O_wt = df_out2_pre['hui_NA2O_wt'].sum()
            var1_sum_meiyan_R_wt = df_out2_pre['meiyan_R_wt'].sum()
            var1_sum_meiyan_Ti_wt = df_out2_pre['meiyan_Ti_wt'].sum()
            if var1_sum_hui_FE2O3_wt == 0:
                peihemei_hui_FE2O3 = None
            else:
                peihemei_hui_FE2O3 = var1_sum_hui_FE2O3 / var1_sum_hui_FE2O3_wt
            if var1_sum_hui_SIO2_wt == 0:
                peihemei_hui_SIO2 = None
            else:
                peihemei_hui_SIO2 = var1_sum_hui_SIO2 / var1_sum_hui_SIO2_wt
            if var1_sum_hui_CAO_wt == 0:
                peihemei_hui_CAO = None
            else:
                peihemei_hui_CAO = var1_sum_hui_CAO / var1_sum_hui_CAO_wt
            if var1_sum_hui_AL2O3_wt == 0:
                peihemei_hui_AL2O3 = None
            else:
                peihemei_hui_AL2O3 = var1_sum_hui_AL2O3 / var1_sum_hui_AL2O3_wt
            if var1_sum_hui_MGO_wt == 0:
                peihemei_hui_MGO = None
            else:
                peihemei_hui_MGO = var1_sum_hui_MGO / var1_sum_hui_MGO_wt
            if var1_sum_hui_TIO2_wt == 0:
                peihemei_hui_TIO2 = None
            else:
                peihemei_hui_TIO2 = var1_sum_hui_TIO2 / var1_sum_hui_TIO2_wt
            if var1_sum_hui_MNO_wt == 0:
                peihemei_hui_MNO = None
            else:
                peihemei_hui_MNO = var1_sum_hui_MNO / var1_sum_hui_MNO_wt
            if var1_sum_hui_P2O5_wt == 0:
                peihemei_hui_P2O5 = None
            else:
                peihemei_hui_P2O5 = var1_sum_hui_P2O5 / var1_sum_hui_P2O5_wt
            if var1_sum_hui_S_wt == 0:
                peihemei_hui_S = None
            else:
                peihemei_hui_S = var1_sum_hui_S / var1_sum_hui_S_wt
            if var1_sum_hui_ZN_wt == 0:
                peihemei_hui_ZN = None
            else:
                peihemei_hui_ZN = var1_sum_hui_ZN / var1_sum_hui_ZN_wt
            if var1_sum_hui_PB_wt == 0:
                peihemei_hui_PB = None
            else:
                peihemei_hui_PB = var1_sum_hui_PB / var1_sum_hui_PB_wt
            if var1_sum_hui_K2O_wt == 0:
                peihemei_hui_K2O = None
            else:
                peihemei_hui_K2O = var1_sum_hui_K2O / var1_sum_hui_K2O_wt
            if var1_sum_hui_NA2O_wt == 0:
                peihemei_hui_NA2O = None
            else:
                peihemei_hui_NA2O = var1_sum_hui_NA2O / var1_sum_hui_NA2O_wt
            if var1_sum_meiyan_R_wt == 0:
                peihemei_meiyan_R = None
            else:
                peihemei_meiyan_R = var1_sum_meiyan_R / var1_sum_meiyan_R_wt
            if var1_sum_meiyan_Ti_wt == 0:
                peihemei_meiyan_Ti = None
            else:
                peihemei_meiyan_Ti = var1_sum_meiyan_Ti / var1_sum_meiyan_Ti_wt

            df_out2 = pd.DataFrame(columns=['SCHEME_NAME',
                                            'H2O', 'ASH', 'COKE_VM', 'S','COKING_COALBLD_BOND_IND','M_L3_JZCHD_Y',
                                            'COKING_COALBLD_GIESFLU', 'L_M_AB', 'C', 'COKE_HOTVALUE','FE2O3','SIO2',
                                            'CAO', 'AL2O3', 'MGO', 'FOP_TIO2','MNO','P2O5','BF_SINTER_QLTY_S','ZN',
                                            'PB', 'FOP_K2O','FOP_NA2O','VITRI_REFL','VITRI_COAL_OI',
                                            'COKE_ASH', 'COKING_COKE_SULCONT', 'COKING_COKE_CSR','COKING_COKE_CRI'])
            df_out1_tmp = df_out1[df_out1['FLAG'] == '统计配合煤']
            df_out1_tmp = df_out1_tmp.reset_index(drop=True)
            peihemei_shui_tmp = df_out1_tmp.loc[0]['H2O']
            peihemei_hui_tmp = df_out1_tmp.loc[0]['ASH']
            peihemei_huifa_tmp = df_out1_tmp.loc[0]['COKE_VM']
            peihemei_liu_tmp = df_out1_tmp.loc[0]['S']
            peihemei_gzhi_tmp = df_out1_tmp.loc[0]['COKING_COALBLD_BOND_IND']
            peihemei_yzhi_tmp = df_out1_tmp.loc[0]['M_L3_JZCHD_Y']
            peihemei_liudong_tmp = df_out1_tmp.loc[0]['COKING_COALBLD_GIESFLU']
            peihemei_pengzhang_tmp = df_out1_tmp.loc[0]['L_M_AB']
            peihemei_cc_tmp = df_out1_tmp.loc[0]['C']
            peihemei_re_tmp = df_out1_tmp.loc[0]['COKE_HOTVALUE']

            dict = {}
            dict['SCHEME_NAME'] = scheme_name
            dict['H2O'] = peihemei_shui_tmp
            dict['ASH'] = peihemei_hui_tmp
            dict['COKE_VM'] = peihemei_huifa_tmp
            dict['S'] = peihemei_liu_tmp
            dict['COKING_COALBLD_BOND_IND'] = peihemei_gzhi_tmp
            dict['M_L3_JZCHD_Y'] = peihemei_yzhi_tmp
            dict['COKING_COALBLD_GIESFLU'] = peihemei_liudong_tmp
            dict['L_M_AB'] = peihemei_pengzhang_tmp
            dict['C'] = peihemei_cc_tmp
            dict['COKE_HOTVALUE'] = peihemei_re_tmp

            dict['FE2O3'] = peihemei_hui_FE2O3
            dict['SIO2'] = peihemei_hui_SIO2
            dict['CAO'] = peihemei_hui_CAO
            dict['AL2O3'] = peihemei_hui_AL2O3
            dict['MGO'] = peihemei_hui_MGO
            dict['FOP_TIO2'] = peihemei_hui_TIO2
            dict['MNO'] = peihemei_hui_MNO
            dict['P2O5'] = peihemei_hui_P2O5
            dict['BF_SINTER_QLTY_S'] = peihemei_hui_S
            dict['ZN'] = peihemei_hui_ZN
            dict['PB'] = peihemei_hui_PB
            dict['FOP_K2O'] = peihemei_hui_K2O
            dict['FOP_NA2O'] = peihemei_hui_NA2O
            dict['VITRI_REFL'] = peihemei_meiyan_R
            dict['VITRI_COAL_OI'] = peihemei_meiyan_Ti
            jiaotan_hui = parm_1 + parm_2 * (peihemei_hui_tmp / 100)/(1 - peihemei_huifa_tmp/100) + parm_3 * (peihemei_huifa_tmp/100)
            jiaotan_liu = parm_4 + parm_5 * (peihemei_liu_tmp / 100)/(1 - peihemei_huifa_tmp/100) - parm_6 * (peihemei_huifa_tmp/100)
            mci = peihemei_hui_tmp*(peihemei_hui_FE2O3*parm_37+peihemei_hui_K2O*parm_38+peihemei_hui_NA2O*parm_39+peihemei_hui_CAO*parm_40+peihemei_hui_MGO*parm_41)/(100-peihemei_huifa_tmp)/(peihemei_hui_SIO2*parm_37+peihemei_hui_K2O*parm_42+peihemei_hui_AL2O3*parm_43+peihemei_hui_TIO2*parm_44)
            jiaotan_csr = parm_17 * QRT2 - parm_19 * mci + parm_20 * (peihemei_huifa_tmp) - parm_21 * (
                peihemei_huifa_tmp) * (peihemei_huifa_tmp) + parm_22 * (peihemei_liudong_tmp) - parm_23 * (
                              peihemei_liudong_tmp) * (peihemei_liudong_tmp) - parm_24 ** (peihemei_liudong_tmp) * (
                              peihemei_huifa_tmp) + parm_25 * peihemei_meiyan_Ti - parm_26
            jiaotan_cri = parm_27 * mci - parm_28 - parm_29 * QRT3 + parm_31 * peihemei_meiyan_R - parm_45 * peihemei_meiyan_R * peihemei_meiyan_R - parm_46 * peihemei_liudong_tmp + parm_47 * peihemei_liudong_tmp * peihemei_meiyan_R - parm_48 * peihemei_meiyan_Ti

            dict['COKE_ASH'] = jiaotan_hui
            dict['COKING_COKE_SULCONT'] = jiaotan_liu
            dict['COKING_COKE_CSR'] = jiaotan_csr
            dict['COKING_COKE_CRI'] = jiaotan_cri

            new_row = pd.Series(dict)
            df_out2 = df_out2.append(new_row, ignore_index=True)



            now = datetime.datetime.now()
            now_1 = now.strftime('%Y%m%d%H%M%S')
            df_out1.drop(['price_wt'], axis=1, inplace=True)
            df_out1.drop(['shui_wt'], axis=1, inplace=True)
            df_out1.drop(['hui_wt'], axis=1, inplace=True)
            df_out1.drop(['huifa_wt'], axis=1, inplace=True)
            df_out1.drop(['liu_wt'], axis=1, inplace=True)
            df_out1.drop(['gzhi_wt'], axis=1, inplace=True)
            df_out1.drop(['yzhi_wt'], axis=1, inplace=True)
            df_out1.drop(['liudong_wt'], axis=1, inplace=True)
            df_out1.drop(['pengzhang_wt'], axis=1, inplace=True)
            df_out1.drop(['cc_wt'], axis=1, inplace=True)
            df_out1.drop(['re_wt'], axis=1, inplace=True)

            df_out1['SCHEME_NAME'] = scheme_name
            df_out1['TMPL_NO'] = tmpl_no
            df_out1['REC_CREATE_TIME'] = now_1
            df_out1['REC_CREATOR'] = 'zzai'
            df_out1['REC_REVISE_TIME'] = now_1
            df_out1['REC_REVISOR'] = 'zzai'
            df_out1_rounded = df_out1.round(3)
            #####存入到数据库
            # df_out1_rounded.to_sql(name='T_ADS_FACT_YLMX_COKE_MODEL_D2'.lower(),
            #                                      con=db_conn_mpp,
            #                                      schema='BG00MAZZAI'.lower(),
            #                                      index=False,
            #                                      if_exists='append',
            #                                      chunksize=10000)

            df_out2['TMPL_NO'] = tmpl_no
            df_out2['REC_CREATE_TIME'] = now_1
            df_out2['REC_CREATOR'] = 'zzai'
            df_out2['REC_REVISE_TIME'] = now_1
            df_out2['REC_REVISOR'] = 'zzai'
            df_out2_rounded = df_out2.round(3)
            #####存入到数据库
            # df_out2_rounded.to_sql(name='T_ADS_FACT_YLMX_COKE_MODEL_S2'.lower(),
            #                                      con=db_conn_mpp,
            #                                      schema='BG00MAZZAI'.lower(),
            #                                      index=False,
            #                                      if_exists='append',
            #                                      chunksize=10000)




            #存到本地测试
            writer = pd.ExcelWriter('炼焦煤分配D' + scheme_name + '.xlsx')
            df_out1_rounded.to_excel(writer, sheet_name='Sheet1', index=False)
            writer.save()
            writer = pd.ExcelWriter('炼焦煤分配S' + scheme_name + '.xlsx')
            df_out2_rounded.to_excel(writer, sheet_name='Sheet1', index=False)
            writer.save()
            return df_out1_rounded, df_out2_rounded

        # sql = " DELETE FROM " \
        #       " BG00MAZZAI.T_ADS_FACT_YLMX_COKE_MODEL_S2" \
        #       " WHERE 1=1 AND TMPL_NO ='%s' " % (tmpl_no)
        # db_conn_mpp.execute(sql)
        # sql = " DELETE FROM " \
        #       " BG00MAZZAI.T_ADS_FACT_YLMX_COKE_MODEL_D2" \
        #       " WHERE 1=1 AND TMPL_NO ='%s'  " % (tmpl_no)
        # db_conn_mpp.execute(sql)
        df_out1_rounded, df_out2_rounded = calc_f2(X=X_chushi, scheme_name='优化方案')
        print('finish')

        pso = PSO(func=calc_f, n_dim=dim, pop=size, max_iter=iter_num, lb=low, ub=up, w=w, c1=c1, c2=c2
                  , constraint_ueq=constraint_ueq, X=XNd, V=V)
        pso.record_mode = True
        pso.run()
        # print('经过PSO计算得到的最优解：',pso.gbest_x)
        best_x = pso.gbest_x
        # print('此时目标函数值为',calc_f(best_x))
        pbest2 = np.concatenate([pso.pbest_x, pso.pbest_y], axis=1)

        idex = np.lexsort([pbest2[:, dim]])
        sorted_data = pbest2[idex, :]

        best_5 = sorted_data[[0, 1, 2, 3, 4]]
        best_5 = np.delete(best_5, -1, 1)
        print(type(best_5[0]))
        ser_x = pd.Series(best_5[0].tolist())
        print(type(ser_x))
        df_out1_rounded1, df_out2_rounded1 = calc_f2(X=ser_x, scheme_name='其他方案1')
        ser_x = pd.Series(best_5[1].tolist())
        print(type(ser_x))
        df_out1_rounded2, df_out2_rounded2 = calc_f2(X=ser_x, scheme_name='其他方案2')
        ser_x = pd.Series(best_5[2].tolist())
        print(type(ser_x))
        df_out1_rounded3, df_out2_rounded3 = calc_f2(X=ser_x, scheme_name='其他方案3')
        ser_x = pd.Series(best_5[3].tolist())
        print(type(ser_x))
        df_out1_rounded4, df_out2_rounded4 = calc_f2(X=ser_x, scheme_name='其他方案4')
        ser_x = pd.Series(best_5[4].tolist())
        print(type(ser_x))
        df_out1_rounded5, df_out2_rounded5 = calc_f2(X=ser_x, scheme_name='其他方案5')

        message = '计算结果入库成功'
        # print('finish')
        return message



